{% comment %}
    Date:       2023-02-07
    Purpose:    Shortcode that will create metrics data for a Steps. The SQL will use the specifict Date and record the counted or totaled amount then write a weekly Sunday's Date - [Count] / [CampusId] / [ThisWeeksSunday] / [TheKey] (used for cleanup) all by Campus partition.
                This code will go backword in time based on the variable WEEKSBACK and update the metric.
    Created by: Trinity Fellowship Church, 5000 Hollywood Rd. Amarillo, TX 79118 
{% endcomment %}
{% if stypeid == 'No' or startcomplete == 'No' or weeksback == 'No' %}
{% else %}
    {% if weeksback <= 0 %}{% assign weeksback = 1 %}{% endif %}
    {% assign thisweekssunday = 'Now' | SundayDate | Date:'MM/dd/yyyy' %}
    {% assign camps = 'Global' | Attribute:'ShortCodeMetricsCampuses','RawValue' | Split:',' %}
    {% assign campsct = camps | Size %}
    {% assign thekey1 = 'Steps>' | Append:stypeid | Append:'>' | Append:startcomplete | Append:'>' %}
    {% assign usc = startcomplete | Downcase %}

    {% if campsct and campsct >= 0 %}

        {% for thiscampus in camps %}

            {% campus where:'Guid == "{{ thiscampus }}"' securityenabled:'false' %}
                {% assign lavacpid = campus.Id %}
                {% assign thekey = thekey1 | Append:lavacpid | Append:'>' %}

                    {% sql %}
                        DECLARE @i AS INT = 0;
                        DECLARE @thisweekssunday AS DATE = '{{ thisweekssunday | DATE:'MM/dd/yyyy' }}';
                        DECLARE @weeksback AS INT = {{ weeksback }}; 
                        DECLARE @propattid AS NVARCHAR(100) = {{ stypeid }}; 
                        DECLARE @lavakey AS NVARCHAR(240) = '{{ thekey }}';
                        DECLARE @cpid AS INT = {{ lavacpid }};
                        DECLARE @startcomplete AS NVARCHAR(20) = '{{ usc }}';

                        WHILE @i <= @weeksback
                            BEGIN
                            DECLARE @rowct AS INT = 0;
                            DECLARE @uwksunday AS DATE = DATEADD(wk,@i*-1,@thisweekssunday);
                            DECLARE @uwkmonday AS DATE = DATEADD(day,-6,@uwksunday);
                            DECLARE @thekey AS NVARCHAR(240) = CAST(@lavakey AS NVARCHAR) + CAST(FORMAT(@uwksunday,'MM/dd/yyyy') AS NVARCHAR);

                            DECLARE @queryliveinsert AS NVARCHAR(max) = '';
                            IF (@startcomplete = 'start')
                                BEGIN
                                    SET @queryliveinsert = 'INSERT INTO [_com_trinityfellowship_MetricsTemp]
                                        OUTPUT INSERTED.ID
                                        SELECT
                                        COUNT(DISTINCT(pa.[PersonId])) AS [Count]
                                        ,st.[CampusId] AS [CampusId]
                                        ,0 AS [Partition001]
                                        ,0 AS [Partition002]
                                        ,0 AS [Partition003]
                                        ,'''
                                        + CONVERT(NVARCHAR(10),@uwksunday,101) + ''' AS [ThisWeeksSunday]
                                        ,''' + @thekey + ''' AS [TheKey]
                                        FROM [Step] st
                                        LEFT JOIN [PersonAlias] pa ON pa.[Id] = st.[PersonAliasId] 
                                        WHERE st.[CampusId] = '
                                        + CAST(@cpid AS NVARCHAR) + ' AND st.[StepTypeId] = '
                                        + CAST(@propattid AS NVARCHAR) + ' AND st.[StartDateTime] >= '''
                                        + CONVERT(NVARCHAR(10),@uwkmonday,101) + ''' AND st.[StartDateTime] < '''
                                        + CONVERT(NVARCHAR(10),DATEADD(day,1,@uwksunday),101) + '''
                                        GROUP BY st.[CampusId]'
                                END
                            ELSE
                                BEGIN
                                    SET @queryliveinsert = 'INSERT INTO [_com_trinityfellowship_MetricsTemp]
                                        OUTPUT INSERTED.ID
                                        SELECT
                                        COUNT(DISTINCT(pa.[PersonId])) AS [Count]
                                        ,st.[CampusId] AS [CampusId]
                                        ,0 AS [Partition001]
                                        ,0 AS [Partition002]
                                        ,0 AS [Partition003]
                                        ,'''
                                        + CONVERT(NVARCHAR(10),@uwksunday,101) + ''' AS [ThisWeeksSunday]
                                        ,''' + @thekey + ''' AS [TheKey]
                                        FROM [Step] st
                                        LEFT JOIN [PersonAlias] pa ON pa.[Id] = st.[PersonAliasId] 
                                        WHERE st.[CampusId] = '
                                        + CAST(@cpid AS NVARCHAR) + ' AND st.[StepTypeId] = '
                                        + CAST(@propattid AS NVARCHAR) + ' AND st.[CompletedDateTime] >= '''
                                        + CONVERT(NVARCHAR(10),@uwkmonday,101) + ''' AND st.[CompletedDateTime] < '''
                                        + CONVERT(NVARCHAR(10),DATEADD(day,1,@uwksunday),101) + '''
                                        GROUP BY st.[CampusId]'
                                END
                                        
                            DECLARE @querylivezero AS NVARCHAR(max) = 'INSERT INTO [_com_trinityfellowship_MetricsTemp]
                                        OUTPUT INSERTED.ID
                                        SELECT 0 AS [Count]
                                        ,' + CAST(@cpid AS NVARCHAR) + ' AS [CampusId]
                                        ,0 AS [Partition001]
                                        ,0 AS [Partition002]
                                        ,0 AS [Partition003]
                                        ,'''
                                        + CONVERT(NVARCHAR(10),@uwksunday,101) + ''' AS [ThisWeeksSunday]
                                        ,''' + @thekey + ''' AS [TheKey]';

                            DECLARE @querycount AS NVARCHAR(max) = '';
                            IF (@startcomplete = 'start')
                                BEGIN
                                    SET @querycount = 'SELECT
                                                        @C = COUNT(DISTINCT(pa.[PersonId]))
                                                        FROM [Step] st
                                                        LEFT JOIN [PersonAlias] pa ON pa.[Id] = st.[PersonAliasId] 
                                                        WHERE st.[CampusId] = '
                                                        + CAST(@cpid AS NVARCHAR) + ' AND st.[StepTypeId] = '
                                                        + CAST(@propattid AS NVARCHAR) + ' AND st.[StartDateTime] >= '''
                                                        + CONVERT(NVARCHAR(10),@uwkmonday,101) + ''' AND st.[StartDateTime] < '''
                                                        + CONVERT(NVARCHAR(10),DATEADD(day,1,@uwksunday),101) + '''
                                                        GROUP BY st.[CampusId]'
                                END
                            ELSE
                                BEGIN
                                    SET @querycount = 'SELECT
                                                        @C = COUNT(DISTINCT(pa.[PersonId]))
                                                        FROM [Step] st
                                                        LEFT JOIN [PersonAlias] pa ON pa.[Id] = st.[PersonAliasId] 
                                                        WHERE st.[CampusId] = '
                                                        + CAST(@cpid AS NVARCHAR) + ' AND st.[StepTypeId] = '
                                                        + CAST(@propattid AS NVARCHAR) + ' AND st.[CompletedDateTime] >= '''
                                                        + CONVERT(NVARCHAR(10),@uwkmonday,101) + ''' AND st.[CompletedDateTime] < '''
                                                        + CONVERT(NVARCHAR(10),DATEADD(day,1,@uwksunday),101) + '''
                                                        GROUP BY st.[CampusId]'
                                END

                            EXEC sp_executesql @querycount, N'@C INT OUTPUT', @C=@rowct OUTPUT

                            IF (@rowct > 0)
                                BEGIN
                                EXEC(@queryliveinsert);
                                END
                            ELSE
                                BEGIN
                                EXEC(@querylivezero);
                                END

                            SET @i = @i + 1
                            END;
                    {% endsql %}
            {% endcampus %}
        {% endfor %}
    {% endif %}

    {% sql %}

        DECLARE @lavakey AS NVARCHAR(240) = '{{ thekey1 }}' + '%';
        DECLARE @thisweekssunday AS DATE = '{{ thisweekssunday | DATE:'MM/dd/yyyy' }}';
        DECLARE @weeksback AS INT = {{ weeksback }}; --Will set w/ Lava
        DECLARE @uwksunday AS DATE = DATEADD(wk,(@weeksback+1)*-1,@thisweekssunday);
        DECLARE @uwkmonday AS DATE = DATEADD(day,-6,DATEADD(wk,(@weeksback+1)*-1,@thisweekssunday));
        
        SELECT SUM([Count]) AS [Count]
        ,[CampusId]
        ,[ThisWeeksSunday]
        FROM [_com_trinityfellowship_MetricsTemp]
        Where [TheKey] LIKE @lavakey AND [ThisWeeksSunday] >= @uwkmonday AND [ThisWeeksSunday] <= @thisweekssunday
        Group By [CampusId],[ThisWeeksSunday]
        ORDER BY [ThisWeeksSunday],[CampusId];

    {% endsql %}

    {% for item in results %}
        {{ item.Count }}, {{ item.ThisWeeksSunday | Date:'MM/dd/yyyy' }}, {{ item.CampusId }},<br>
    {% endfor %}

    {% capture reply %}
        {[ metricscleanup thekey:'{{ thekey1 }}']}
    {% endcapture %}
   

{% endif %}